﻿/*******************************************************************************
 * Copyright @ 2017 YunSpace.Framework 版权所有
 * Author: GaoTang
 * Description: YunSpace 快速开发平台
 * Website：http://www.yunspace.com.cn
*********************************************************************************/
#region USING SYSTEM
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using YunSpace.Core;
using YunSpace.Data;
using YunSpace.Domain.Entity.SiteLibraryManage;
using YunSpace.Domain.IRepository.SiteLibraryManage;
using YunSpace.Domain.ViewModel;
using YunSpace.Domain.ViewModel.MiniSite;
using YunSpace.Domain.ViewModel.StatisticalForm;
using YunSpace.Domain.ViewModel.SiteMgr.Pdf;

#endregion

namespace YunSpace.Repository.SiteLibraryManage
{
    public class SiteRepository : RepositoryBase<SiteEntity>, ISiteRepository
    {
        #region 小场地逻辑
        public List<SiteHotActionModel> GetHotSites(string lastId, int count)
        {
            using (var db = new RepositoryBase())
            {
                var strSql = @"SELECT a.f_id,a.title,c.inner_price,a.brand_story,c.market_price,b.path f_PhotoUrl,5 starnumber,case when fa.f_id is null then 0 else 1 end isfavorite FROM SL_Site a
                         inner join (select max(a.F_Id) F_Id,max(a.inner_price) inner_price,max(a.market_price) market_price,a.site_id from SL_Space a group by a.site_id) c on a.id=c.site_id   
                         left join SL_Photos b on a.f_id=b.photoable_id and b.photoable_type='Site' and b.is_cover=1
                         left join MiniSite_Favorite fa on a.F_Id=fa.f_favoriteid 
                         WHERE a.F_Category = 1 {0}
                         order by a.F_CreatorTime DESC limit 0,{1}";
                if (lastId.IsNullOrEmpty())
                {
                    strSql = string.Format(strSql, "", count);
                    return db.FindList<SiteHotActionModel>(strSql);
                }
                else
                {
                    strSql = string.Format(strSql, "AND a.F_Id > @fId", count);
                    DbParameter[] parameter =
                    {
                         new MySqlParameter("@fId", lastId)
                    };
                    return db.FindList<SiteHotActionModel>(strSql, parameter);
                }
            }
        }

        public List<SiteHotActionModel> GetHotSitesByIds(List<string> ids)
        {
            using (var db = new RepositoryBase())
            {
                var strSql = @"SELECT a.f_id,a.title,c.inner_price,a.brand_story,c.market_price,b.path f_PhotoUrl,5 starnumber,case when fa.f_id is null then 0 else 1 end isfavorite FROM SL_Site a
                         inner join (select max(a.F_Id) F_Id,max(a.inner_price) inner_price,max(a.market_price) market_price,a.site_id from SL_Space a group by a.site_id) c on a.id=c.site_id   
                         left join SL_Photos b on a.f_id=b.photoable_id and b.photoable_type='Site' and b.is_cover=1
                         left join MiniSite_Favorite fa on a.F_Id=fa.f_favoriteid
                         WHERE a.F_Category = 1 and a.f_id in({0})";
                strSql = string.Format(strSql, string.Join(",", ids.Select(p => string.Format("'{0}'", p))));
                return db.FindList<SiteHotActionModel>(strSql);
            }
        }

        public List<SiteEntity> GetSites(string lastId, int count)
        {
            var strSql = @"SELECT *
                            FROM SL_Site site
                            WHERE site.F_Category = 1 {0}
                            ORDER BY site.F_CreatorTime DESC
                            LIMIT 0, {1}";
            if (lastId.IsNullOrEmpty())
            {
                strSql = string.Format(strSql, "", count);
                return FindList(strSql);
            }
            else
            {
                strSql = string.Format(strSql, "AND site.F_Id > @fId", count);
                DbParameter[] parameter =
                {
                     new MySqlParameter("@fId", lastId)
                };
                return FindList(strSql, parameter);
            }
        }

        public List<SiteEntity> GetSitesByIds(List<string> ids)
        {
            var strSql = @"SELECT *
                            FROM SL_Site site
                            WHERE site.F_Category = 1 AND site.F_Id IN({0})
                            ORDER BY site.F_CreatorTime DESC";
            strSql = string.Format(strSql, string.Join(",", ids.Select(p => string.Format("'{0}'", p))));

            return FindList(strSql);
        }

        #endregion

        #region charts

        public List<SiteMapDto> GetSiteMaps()
        {
            const string sql = @"SELECT a.F_Id, a.F_FullName, COUNT(1) AS Count
                                FROM Sys_Area a
                                    INNER JOIN SL_Site s ON s.provinces_id = a.F_Id
                                WHERE (a.F_DeleteMark IS NULL || a.F_DeleteMark = 0)
                	                AND (s.F_DeleteMark IS NULL || s.F_DeleteMark = 0)
					                AND s.state = 1
                                    AND s.created_at IS NOT NULL AND DATE_FORMAT(s.created_at, '%Y') >= 2015
                                GROUP BY a.F_Id, a.F_FullName";

            using (var respository = new RepositoryBase())
            {
                return respository.FindList<SiteMapDto>(sql);
            }
        }

        /// <summary>
        /// 获取场地柱状图列表信息
        /// </summary>
        /// <param name="provinceId"></param>
        /// <returns></returns>
        public List<SiteBarDto> GetSiteBarData(string provinceId)
        {
            var sb = new StringBuilder();
            sb.Append(@"SELECT d.F_ItemName AS Name, COUNT(1) AS Count
                        FROM SL_Site s 
                        INNER JOIN Sys_ItemsDetail d ON d.F_ItemCode = s.site_type
                        INNER JOIN Sys_Items i ON d.F_ItemId = i.F_Id
                        WHERE");
            sb.Append(provinceId.IsNullOrEmpty() ? " 1 = 1" : $" s.provinces_id = '{provinceId}'");
            sb.Append(@" AND i.F_EnCode = 'SiteType' AND s.state = 1
                    GROUP BY s.site_type, d.F_ItemName ");

            using (var respository = new RepositoryBase())
            {
                return respository.FindList<SiteBarDto>(sb.ToString());
            }
        }

        /// <summary>
        /// 获取场地柱状图和折线列表信息
        /// </summary>
        /// <param name="provinceId"></param>
        /// <returns></returns>
        public List<SiteBarDto> GetSiteBarAndLineData(string provinceId)
        {
            var sb = new StringBuilder();
            sb.Append(@"SELECT d.F_ItemName AS Name, DATE_FORMAT(s.created_at, '%Y') AS Year, COUNT(1) AS Count
                        FROM SL_Site s 
                        INNER JOIN Sys_ItemsDetail d ON d.F_ItemCode = s.site_type
                        INNER JOIN Sys_Items i ON d.F_ItemId = i.F_Id
                        WHERE");
            sb.Append(provinceId.IsNullOrEmpty() ? " 1 = 1" : $" s.provinces_id = '{provinceId}'");
            sb.Append(@" AND i.F_EnCode = 'SiteType' AND s.state = 1 AND s.created_at IS NOT NULL AND DATE_FORMAT(s.created_at, '%Y') >= 2015
                        GROUP BY s.site_type, d.F_ItemName, DATE_FORMAT(s.created_at, '%Y') ;");

            using (var respository = new RepositoryBase())
            {
                return respository.FindList<SiteBarDto>(sb.ToString());
            }
        }




        #endregion charts

        #region PDF 信息

        public CreateSitePdfDto GetSitePdfDto(string siteId)
        {
            var sql = $@"SELECT  s.title AS SiteName,
                                s.brand_story AS brandStory ,
                                s.site_style ,
                                d.F_ItemName AS siteStyle ,
                                s.recommend_reason AS RecommendReason ,
                                s.city_id ,
                                a2.F_FullName AS City ,
                                s.district_id ,
                                a3.F_FullName AS District ,
                                s.address ,
                                s.business_area ,
                                a4.F_FullName AS BusinessArea ,
                                s.subway_distance AS subwayDistance ,
                                s.loop_distance AS loopDistance ,
                                s.rent_office_building AS rentOfficeBuilding ,
                                s.rent_residential_building AS rentResidentialBuilding,
                                s.level AS Level,
                                s.F_Facilities AS Facilities
                        FROM    SL_Site AS s                                
                                LEFT JOIN Sys_ItemsDetail AS d ON d.F_ItemId = 'a9ca61e9-e68c-42e9-9f54-12d90d91b619'
                                                                  AND d.F_ItemCode = s.site_style
                                LEFT JOIN Sys_Area AS a2 ON a2.F_Id = s.city_id
                                LEFT JOIN Sys_Area AS a3 ON a3.F_Id = s.district_id
                                LEFT JOIN Sys_Area AS a4 ON a4.F_Id = s.business_area
                        WHERE   s.F_Id = '{siteId}';";
            return FindEntity<CreateSitePdfDto>(sql);
        }

        public List<SpaceMarketAttrPdfDto> GetSpaceMarketAttrPdfDtos(string siteId)
        {
            var sql = $@"SELECT  a.F_StoreType ,
                                d.F_ItemName AS StoreType ,
                                a.F_GuestType ,
                                d2.F_ItemName AS GuestType ,
                                a.sign_brand AS SignBrand
                        FROM    SL_Site AS s
                                LEFT JOIN SL_Space AS s2 ON s2.F_SiteId = s.F_Id
                                LEFT JOIN Space_Market_Attrs AS a ON a.F_SpaceId = s2.F_Id
                                LEFT JOIN Sys_ItemsDetail AS d ON d.F_ItemId = '52d87fb0-358c-41ad-9897-1cb82f9e8b5a'
                                                                  AND d.F_ItemCode = a.F_StoreType
                                LEFT JOIN Sys_ItemsDetail AS d2 ON d2.F_ItemId = '22613e6c-151b-42c7-8288-7cfb4e3d678b'
                                                                   AND d2.F_ItemCode = a.F_GuestType
                        WHERE   s.F_Id = '{siteId}';";
            return GetColumnList<SpaceMarketAttrPdfDto>(sql);
        }

        public List<SpacePdfDto> GetSpacePdfDtos(string siteId)
        {
            var sql = $@"SELECT  s.F_Id AS Id,
                                s.F_Facilities AS Facilities,
                                s.area AS Area,
                                s.title AS SpaceName ,
                                s.length_width AS LengthWidth ,
                                s.current_floor AS CurrentFloor ,
                                s.height AS Height ,
                                s.limit_height AS LimitHeight ,
                                s.max_seating_capacity AS MaxSeatingCapacity ,
                                s.vehicle AS Vehicle ,
                                s.visitors AS Visitors ,
                                s.F_Facilities AS Facilities ,
                                s.weekend_flow AS WeekendFlow ,
                                s.F_ordinary_MarketPrice AS OrdinaryMarketPrice ,
                                s.F_MarketPrice AS MarketPrice ,
                                s.F_PriceMarketBuild AS PriceMarketBuild
                        FROM    SL_Space AS s
                        WHERE   s.F_SiteId = '{siteId}';";
            return GetColumnList<SpacePdfDto>(sql);
        }

        public List<ActivitiesCasePdfDto> GetActivitiesCasePdfDtos(string siteId, string spaceId)
        {
            var sql = $@"SELECT  c.F_Id AS Id ,
                                c.F_SiteId AS SiteId ,
                                c.F_SpaceId AS SpaceId ,
                                c.F_ActivitiesTitle AS ActivitiesTitle ,
                                c.F_StarDate AS StarDate ,
                                c.F_EndDate AS EndDate
                        FROM    Site_ActivitiesCase AS c
                        WHERE   c.F_SiteId = '{siteId}'
                                AND c.F_SpaceId = '{spaceId}';";
            return GetColumnList<ActivitiesCasePdfDto>(sql);
        }

        public List<PhotoPdfDto> GetPhotoPdfDtos(string photoableId,string photoableType)
        {
            var sql = $@"SELECT  photoable_type AS PhotoableType ,
                                F_path AS Path, tag AS Tag
                        FROM    SL_Photos
                        WHERE   photoable_id = '{photoableId}'
                            AND photoable_type = '{photoableType}'
		                    AND (F_DeleteMark IS NULL || F_DeleteMark  = 0); ";
            return GetColumnList<PhotoPdfDto>(sql);
        }

        public SpaceSupplementPdfDto GetSpaceSupplementPdfDtos(string spaceId)
        {
            var sql = $@"SELECT  accept_weight AS AcceptWeight ,
                                title AS Title,
                                ceiling AS Ceiling ,
                                some_digits AS SomeDigits ,
                                largest_digits AS LargestDigits ,
                                Fixed_Seat_Number AS FixedSeatNumber ,
                                ( CASE F_IsNightPlays
                                    WHEN 1 THEN '是'
                                    ELSE '否'
                                  END ) AS IsNightPlays ,
                                F_PriceNightMarketBuild AS PriceNightMarketBuild ,
                                deposit_time AS DepositTime ,
                                build_time AS BuildTime ,
                                F_PropertyFee AS PropertyFee ,
                                F_CostWater AS CostWater ,
                                power_supply AS PowerSupply ,
                                cost_Cleaning AS CostCleaning ,
                                cost_security AS CostSecurity ,
                                cost_approval_forecast AS CostApprovalForecast ,
                                F_OtherPrice AS OtherPrice ,
                                remark AS Remark
                        FROM    SL_Space AS s
                        WHERE   s.F_Id = '{spaceId}';";
            return FindEntity<SpaceSupplementPdfDto>(sql);
        }

        #endregion PDF 信息

    }
}

